﻿--QUAN LY CAN TIN
USE MASTER
GO

IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME = 'quanlycantin')
DROP DATABASE quanlycantin
GO

CREATE DATABASE quanlycantin
GO


SET DATEFORMAT dmy
GO

 -- Dung quanlythietbi, tao cac bang du lieu.
USE quanlycantin

GO


--TAI_KHOAN
CREATE TABLE TAI_KHOAN
(
	USEID 		VARCHAR(20)	 NOT NULL,
	PASSID			VARCHAR(50) NOT NULL,
	PERMISSION		int,
	PRIMARY KEY (USEID)
)
go

INSERT INTO TAI_KHOAN VALUES('admin', 'admin', 1)

GO

--LOAI THUC AN NUOC UONG
CREATE TABLE LOAI_TANU
(
	MA_LOAI				CHAR  (4)	NOT NULL,
	TEN_LOAI			NVARCHAR (30)	NULL,
	CONSTRAINT PK_LANU PRIMARY KEY (MA_LOAI)
)

GO

INSERT INTO LOAI_TANU VALUES('L001', N'CƠM' )
INSERT INTO LOAI_TANU VALUES('L002', N'NƯỚC NGỌT' )
INSERT INTO LOAI_TANU VALUES('L003', N'BÚN' )
INSERT INTO LOAI_TANU VALUES('L004', N'CHÁO' )
INSERT INTO LOAI_TANU VALUES('L005', N'BÁNH NGỌT' )
INSERT INTO LOAI_TANU VALUES('L006', N'BÁNH MÌ' )

GO

CREATE TABLE BAN
(
	MA_BAN				CHAR  (4)	NOT NULL,
	TEN_BAN				NVARCHAR (30)	NULL,
	SL_CHO				INT,
	VI_TRI				NVARCHAR(20),
	CONSTRAINT PK_BAN PRIMARY KEY (MA_BAN)
)
GO

INSERT INTO BAN VALUES('B001',N'BÀN SỐ 1', 5, N'TRUNG TÂM')	;
INSERT INTO BAN VALUES('B002',N'BÀN SỐ 2', 6, N'TRUNG TÂM')	;
INSERT INTO BAN VALUES('B003',N'BÀN SỐ 3', 7, N'TRUNG TÂM')	;
INSERT INTO BAN VALUES('B004',N'BÀN SỐ 4', 8, N'TRUNG TÂM')	;
INSERT INTO BAN VALUES('B005',N'BÀN SỐ 5', 9, N'TRUNG TÂM')	;

GO

CREATE TABLE THUC_AN_NUOC_UONG
(
	MA_TANU				CHAR    (4)	NOT NULL,
	MA_LOAI CHAR(4) CONSTRAINT FK_MA_LOAI_TANU FOREIGN KEY REFERENCES LOAI_TANU(MA_LOAI),
	TEN_TANU	NVARCHAR (30)	NULL,
	DON_GIA		MONEY, 
	CONSTRAINT PK_TANU PRIMARY KEY (MA_TANU)
)
GO

INSERT INTO THUC_AN_NUOC_UONG VALUES('F001','L001',N'CƠM SƯỜN',10000);
INSERT INTO THUC_AN_NUOC_UONG VALUES('F002','L001',N'CƠM GÀ CHIÊN BƠ',30000);
INSERT INTO THUC_AN_NUOC_UONG VALUES('F003','L001',N'CƠM BÒ KHO',40000);
INSERT INTO THUC_AN_NUOC_UONG VALUES('F004','L001',N'CƠM CHIÊN DƯƠNG CHÂU',20000);
INSERT INTO THUC_AN_NUOC_UONG VALUES('F005','L001',N'CƠM VỊT KHO GỪNG',30000);

GO

--NHAN VIEN
CREATE TABLE NHAN_VIEN
(
	MA_NV		CHAR(4)	NOT NULL,
	HO_TEN_NV	NVARCHAR (50)	NULL,
	TEN_DANG_NHAP VARCHAR(20) CONSTRAINT FK_NHAN_VIEN_TAI_KHOAN FOREIGN KEY REFERENCES TAI_KHOAN(USEID),
	NGAY_SINH		DATETIME,
	GIOI_TINH	NVARCHAR(5),
	DIA_CHI		NVARCHAR (50)	NULL,
	SDT		VARCHAR (13)	NULL,
	CHUC_VU		NVARCHAR (20),
	CONSTRAINT PK_NHAN_VIEN PRIMARY KEY (MA_NV)
)
GO

INSERT INTO NHAN_VIEN VALUES('NV01',N'NGUYỄN THỊ HUYỀN TRÂN', 'admin','15/05/1990', N'NỮ',N'VĨNH LONG', '0932885583', N'QUẢN LÝ');
INSERT INTO NHAN_VIEN VALUES('NV02',N'NGUYỄN THỊ HỒNG VÂN', 'admin','15/05/1990', N'NỮ',N'VĨNH LONG', '0932885583', N'QUẢN LÝ');
INSERT INTO NHAN_VIEN VALUES('NV03',N'NGUYỄN THANH PHONG', 'admin','15/05/1990', N'NỮ',N'VĨNH LONG', '0932885583', N'QUẢN LÝ');
INSERT INTO NHAN_VIEN VALUES('NV04',N'NGUYỄN THỊ THU THỦY', 'admin','15/05/1990', N'NỮ',N'VĨNH LONG', '0932885583', N'QUẢN LÝ');

GO

--KHACH HANG
CREATE TABLE KHACH_HANG
(
	MA_KH		CHAR(4)	NOT NULL,
	HO_TEN_KH	NVARCHAR (50)	NULL,
	NG_SINH		DATETIME,
	G_TINH	NVARCHAR(5),
	D_CHI		NVARCHAR (50)	NULL,
	DIEN_THOAI		VARCHAR (13)	NULL,
	CONSTRAINT PK_KH PRIMARY KEY (MA_KH)
)
GO
INSERT INTO KHACH_HANG VALUES('KH01',N'PHẠM HỒNG ÂN','03/03/1990',N'NAM','CẦN THƠ','0932885539');
INSERT INTO KHACH_HANG VALUES('KH02',N'PHẠM THANH AN','03/03/1990',N'NAM','CẦN THƠ','0932885539');
INSERT INTO KHACH_HANG VALUES('KH03',N'PHẠM ĐĂNG KHO','03/03/1990',N'NAM','CẦN THƠ','0932885539');

GO
--HOA DON
CREATE TABLE HOA_DON
(
	STT_HD				CHAR(4)   NOT NULL,
	NGAY_LAP			DATETIME ,	
	MA_NV				CHAR(4)		NOT NULL
	CONSTRAINT FK_HD_NHAN_VIEN FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV),
	
	MA_KH				CHAR(4)		NOT NULL
	CONSTRAINT FK_HD_KH FOREIGN KEY REFERENCES KHACH_HANG(MA_KH),

	
	TC	MONEY,
	CONSTRAINT PK_PMT PRIMARY KEY (STT_HD)
)
GO
INSERT INTO HOA_DON VALUES('HD01','10/11/1990','NV01','KH01',20000)
INSERT INTO HOA_DON VALUES('HD02','10/11/1990','NV01','KH01',20000)


CREATE TABLE CHI_TIET_HD
(
	STT_HD				CHAR(4)	
	CONSTRAINT FK_CTHD_HD FOREIGN KEY REFERENCES HOA_DON(STT_HD),
	
	MA_BAN				CHAR(4)		NOT NULL
	CONSTRAINT FK_HD_BAN FOREIGN KEY REFERENCES BAN(MA_BAN),

	MA_TANU				CHAR(4)
	CONSTRAINT FK_CTHD_TANU FOREIGN KEY REFERENCES THUC_AN_NUOC_UONG(MA_TANU),

	SL			INT,
	
	CONSTRAINT PK_CTHD PRIMARY KEY (STT_HD, MA_TANU)
)
GO
INSERT INTO CHI_TIET_HD VALUES('HD01','B001','F001',2);
INSERT INTO CHI_TIET_HD VALUES('HD02','B002','F001',2);










